package cn.org.timespace.easyframe.util;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;

import cn.org.timespace.easyframe.DictionaryConst;
import cn.org.timespace.easyframe.SystemConst;
import cn.org.timespace.easyframe.model.permission.EfOrg;
import cn.org.timespace.easyframe.model.permission.EfUser;
import cn.org.timespace.easyframe.util.datatime.DateTimeUtils;
import cn.org.timespace.easyframe.util.pager.Pager;

public class DaoUtil {

	//此变量为真则打印信息
	public static boolean debug = false;
	
	public static final Log log = LogFactory.getLog(DaoUtil.class);
	
	public  static final String ORG_CODE = "org.code";
	
	public static final String TEAM = "team";
	public static final String PERSONAL = "personal";
	
	public static final String TYPE_STRING = "string";
	public static final String TYPE_NUMBER = "int";
	
	public static final int inConditionMaxCount = 2;//select语句的in条件，一次最多包含多少项
	
	public static List<Map<String,Object>> selectBySqlForPage(String sql,Session session,Pager pager){
		
		List<Map<String,Object>> list = null;
		
		try{
		
		SQLQuery sqlQuery = session.createSQLQuery(sql);
		sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		
		//分页的参数
		int currentPage = pager.getCurrentPageNum();//当前页
		int itemPerPage = pager.getItemPerPage();//每页行数
		sqlQuery.setFirstResult((currentPage-1)*itemPerPage);
		sqlQuery.setMaxResults(itemPerPage);
		
		list = sqlQuery.list();
		if(list==null)
			list = new ArrayList<Map<String,Object>>();
		
		//获得总行数
		sql = "select count(*) c from ("+sql+")";
		sqlQuery = session.createSQLQuery(sql);
		sqlQuery.setResultTransformer(Transformers.TO_LIST);
		
		List<List> rowCountlist = sqlQuery.list();
		int totalCount = Integer.parseInt(((BigDecimal)(rowCountlist.get(0).get(0))).toString());
		
		pager.setTotalCount(totalCount);
		pager.setData(list);
		
		if(debug){
			printListContainsMap(list);
			System.out.println("总记录数："+totalCount);
		}
		
		}catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}
		
		return list;
	}
	
	
	public static List<Map<String,Object>> selectBySql(String sql,Session session){
		
		List<Map<String,Object>> list = null;
		
		try{
			
			SQLQuery sqlQuery = session.createSQLQuery(sql);
			sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			
//			Query sqlQuery = session.createQuery(sql);
			
			list = sqlQuery.list();
			if(list==null)
				list = new ArrayList<Map<String,Object>>();
			
			if(debug){
				printListContainsMap(list);
			}
		
		}catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}
		
		return list;
	}
	
	//从SQL中查询一个值（第一行的第一个值）
	public static Object selectOneValueFromSql(String sql,Session session){
		
		Object rs = null;
		
		try{
			
			SQLQuery sqlQuery = session.createSQLQuery(sql);
			sqlQuery.setResultTransformer(Transformers.TO_LIST);
			
			List<List> list = sqlQuery.list();
			if(list!=null&&list.size()>0)
				rs = list.get(0).get(0);
			else
				rs = null;
		
		}catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}
		
		return rs;
	}
	
	//根据传入的项目码，返回项目ID
	public static String getItemIdByItemCode(String itemCode,Session session){
		String rs = null;
		String sql = null;
		try{
			sql = "select ITEM_ID from REVENUR_ITEMS where ITEM_CODE='"+
			itemCode+"'";
			rs =  DaoUtil.selectOneValueFromSql(sql, session).toString();
		}catch (Exception e) {
			System.out.println("有异常的SQL："+sql);
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		return rs;
	}
	
	public static void printListContainsMap(List<Map<String,Object>> list){
		
		int i = 0;
		
		for(Map<String,Object> map : list){
			
//			System.out.println("******************行"+(++i));
			
			Set<String> set  = map.keySet();
			for(String key:set){
				Object value = map.get(key);
				System.out.println(key+"----"+value);
			}
		}
	}
	
	//获取位数正确位数正常的序列号
	public static String formatSerialNo(Long inputNo){
		String s = "00000000000000000"+inputNo;
		return s.substring(s.length()-SystemConst.accountSerialNoLength);
	}
	
	
	//生成协议单位的帐号
	public static String getAgreementUnitAccount(Session session){
		String account = null;
		
		try{
			
			SimpleDateFormat DateFormat = new SimpleDateFormat("yyMMdd"); 
			String date =DateFormat.format(new Date()); 
			String accountHeader = "A"+date;
			
			String hql = "select billAccount from AgreementUnitManage where "+
							"billAccount like '"+accountHeader+"%'";
			Query query = session.createQuery(hql);
			List<String> list = query.list();
			List<Long> accountList = new ArrayList<Long>();
			
			//如果今天已经有新建帐号
			if(list!=null&&list.size()>0){
				
				for(String s:list){
					String tmp = s.replaceAll("A", "");
					Long oneAccountNum = 
						Long.parseLong(tmp);
					accountList.add(oneAccountNum);
				}
				
				account = accountHeader
					+formatSerialNo(MathUtils.getMaxNumFromNumList(accountList)+1);//流水号
				
			}else{//如果为当天第一个帐号
				account = accountHeader+formatSerialNo(1l);
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
			throw new RuntimeException(ex.getMessage());
		}
		
		return account;
	}
	
	
	//生成协议单位编码
	public static String getAgreementUnitCode(Session session,String dicUnitType){
		String code = "";
		String headChar="";
		
		try{
			//政府单位（Z），旅行社（L），旅游公司（C），其他（O）
			if(DictionaryConst.DIC_UNIT_TYPE_GOVERNMENT.equals(dicUnitType)){
				headChar = "Z";
			}else if(DictionaryConst.DIC_UNIT_TYPE_TRAVEL_COMPANY.equals(dicUnitType)){
				headChar = "C";
			}else if(DictionaryConst.DIC_UNIT_TYPE_TRAVEL_AGENT.equals(dicUnitType)){
				headChar = "L";
			}else if(DictionaryConst.DIC_UNIT_TYPE_OTHER.equals(dicUnitType)){
				headChar = "O";
			}			
			
			String codeHeader = headChar+DateTimeUtils.formatDate(new Date(), "yyMMdd");
			
			String hql = "select code from AgreementUnitManage where code like '"+codeHeader+"%'";
			Query query = session.createQuery(hql);
			List<String> list = query.list();
			List<Long> accountList = new ArrayList<Long>();
			
			//如果今天已经有新建帐号
			if(list!=null&&list.size()>0){
				
				for(String s:list){
					String tmp = s.substring(1, s.length());
					Long oneAccountNum = 
						Long.parseLong(tmp);
					accountList.add(oneAccountNum);
				}
				
				code = codeHeader+formatSerialNo(MathUtils.getMaxNumFromNumList(accountList)+1);//流水号
				
			}else{//如果为当天第一个帐号
				code = codeHeader+formatSerialNo(1l);
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
			throw new RuntimeException(ex.getMessage());
		}
		
		return code;
	}
	
	
	
	//获得以开始时间与结束时间为条件的SQL
	public static String getDateCondition(
											HttpServletRequest request,
											String startTimeName,//开始时间名称
											String endTimeName,//结束时间名称
											String colName){//使用条件的字段名
		
		String condition = null;
		String startTime = request.getParameter(startTimeName);
		String endTime = request.getParameter(endTimeName);
		
		if(StringUtils.isEmpty(startTime)){
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			startTime=sdf.format(new Date());
			request.setAttribute(startTimeName, startTime);
		}
		
		if(StringUtils.isEmpty(endTime)){
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			endTime=sdf.format(new Date());
			request.setAttribute(endTimeName, endTime);
		}
		
		condition = " and ("+colName+">=to_date('"+startTime+"','yyyy-mm-dd')"+
		"and "+colName+"<(to_date('"+endTime+"','yyyy-mm-dd')+1))";
		
		return condition;
	}
	
	//获得以开始时间与结束时间为条件的SQL
	public static String getDateCondition(
			String startTime,//开始时间名称
			String endTime,//结束时间名称
			String colName){//使用条件的字段名
		
		String condition = null;
		
		if(StringUtils.isEmpty(startTime)){
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			startTime=sdf.format(new Date());
		}
		
		if(StringUtils.isEmpty(endTime)){
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			endTime=sdf.format(new Date());
		}
		
		condition = " and ("+colName+">=to_date('"+startTime+"','yyyy-mm-dd')"+
		"and "+colName+"<(to_date('"+endTime+"','yyyy-mm-dd')+1))";
		
		return condition;
	}
	
	
		
	
	
	//根据传入的 groupname ,去字典表中找组中最大的itemValue 只针对type 为table 类型
	public static int getMaxItemValueInGroup(Session session,String groupname){
		
		int maxItemValue = 1;
		
		String hql = null;
		Query q = null;
		List queryResultList = null;
		
		hql = "select e.id.itemvalue from EfDictionary e where e.id.groupname=? order by to_number(e.id.itemvalue) desc";
		q = session.createQuery(hql);
		q.setString(0, groupname);
		queryResultList = q.list();
		
		if(null!=queryResultList && queryResultList.size()>0){
			String currMax = queryResultList.get(0).toString();
			maxItemValue = Integer.parseInt(currMax);
		}
		
		return maxItemValue;
		
	}
	
	
	
	//获得用户ID与对应名字的 Map
	public static Map<String,String> getEfUserIdNameMap(Session session){
		
		Map<String,String> map = new HashMap<String,String>();
		
		String hql = "from EfUser";
		Query q = session.createQuery(hql);
		List<EfUser> list = q.list();
		for(EfUser e:list){
			map.put(e.getUserId(), e.getUserName());
		}
		
		return map;
		
	}
	
	
	
	//根据传入的房间ID，返回当前使用房间的入住单ID
	public static String getCheckInBillIdByRoomId(String roodId,//房间ID
													Session session){//连接源
		String checkInBillId = null;
		
		String sql = "select CHECK_IN_BILL.Check_In_Id"
				+ " from CHECK_IN_BILL,ROOM_CHECK_IN_REL"
				+ " where "
				+ " CHECK_IN_BILL.Check_In_Id = ROOM_CHECK_IN_REL.Check_In_Bill_Id"
				+ " and CHECK_IN_BILL.DIC_CHECK_IN_STATE = 1"
				+ " and ROOM_CHECK_IN_REL.DIC_ROOM_FLAG = 1"
				+ " and ROOM_CHECK_IN_REL.Room_Manage_Id = '"+roodId+"'";
		
		checkInBillId = selectOneValueFromSql(sql, session).toString();
		
		return checkInBillId;
	}
	
	
	//判断房间是否是入住状态
	public static boolean checkWhetherIsLivingRoom(String roomId,Session session){
		
		boolean rs = false;
		
		String hql = "from RoomManage where dicRoomState in('"+DictionaryConst.DicRoomState_CheckIN+"','"+DictionaryConst.DicRoomState_Leave+"') and roomId=?";
		Query query = session.createQuery(hql);
		query.setString(0, roomId);
		List list = query.list();
		if(list!=null && list.size()>0){
			rs = true;
		}
	
		
		return rs;
	}
	
	
	
	
	//当前在住房间入住类型 Map<roomId,dicCheckInType>
	public static Map<String,String> getNowLiveRoomCheckInType(Session session ){
		try {
			
			String hql = null;
			Query q = null;
			List list = null;
			
			Map<String,String> map = new HashMap<String,String>();
			
			List<String> roomStateList  = new ArrayList<String>();
			roomStateList.add(DictionaryConst.DicRoomState_CheckIN);//入住
			roomStateList.add(DictionaryConst.DicRoomState_Leave);//预离
			
			hql = " select rc.roomManage.roomId, c.dicCheckInType  " +
			" from CheckInBill c join c.roomCheckInRels rc  " +
			" where 1=1 " +
			" and rc.roomManage.dicRoomState in(:roomStateList)"+
			" and rc.dicRoomFlag=:dicRoomFlag " + 
			" and c.dicCheckInState =:dicCheckInState " ;
			
			q = session.createQuery(hql);
			q.setString("dicCheckInState", DictionaryConst.DIC_CHECK_IN_STATE_HAS_NOT_SETTLED);//未结算
			q.setString("dicRoomFlag", DictionaryConst.DIC_ROOM_FLAG_NORMALCHECKIN);//正常入住
			q.setParameterList("roomStateList", roomStateList);//入住，预离
			
			list = q.list();
			
			if(0==list.size()) return map;//没有入住房间
			
			for(int i=0;i<list.size();i++){
				Object[] objArr  = (Object[]) list.get(i);
				String roomId = (String) objArr[0];
				String dicCheckInType = (String) objArr[1];
				map.put(roomId, dicCheckInType);
			}

			return map;
			
		} catch (RuntimeException re) {
			throw re;
		}
		
	}
	

	//对象是否为空
	 public static  boolean isNull(Object obj)
    {
        boolean result = true;
        if(obj != null)
        {
            result = false;
            if(obj.getClass().getName().equals("java.lang.String") && ((String)obj).length() == 0)
                result = true;
        }
        return result;
    }
	 
	 

	//入住天数
	public static int getLiveDays(Date startDate,Date endDate) throws ParseException{
		
		SimpleDateFormat formdate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		Calendar c = Calendar.getInstance();
		c.setTime(endDate);
		String s = c.get(Calendar.YEAR)+"-"+(c.get(Calendar.MONTH)+1)+"-"+c.get(Calendar.DAY_OF_MONTH)+" 00:00:00";
		Date endDate2 = formdate.parse(s);
		
		Long days = 0L;
		Long betweenTime  = endDate2.getTime()-startDate.getTime();
		
		if(betweenTime<0L){
			days = 0L;
		}else{
			days = betweenTime/(24*60*60*1000);
			Long remainder = betweenTime%(24*60*60*1000);
			if(remainder>0){
				days+=1;
			}
		}
		return Integer.parseInt(days.toString());
		
	}
	
	
	//入住小时
	public static int getLiveHours(Date startDate,Date endDate) throws ParseException{
			
			Long betweenTime  = endDate.getTime()-startDate.getTime();
			
			Long hours = betweenTime/(60*60*1000);
			Long remainder = betweenTime%(60*60*1000);
			if(remainder>0){
				hours+=1;
			}
			return Integer.parseInt(hours.toString());
			
	}
	
	//增加天数
	public static Date addDays(Date srcd,int days) throws ParseException{
		Date d = null;
		
		Calendar c = Calendar.getInstance();
		c.setTime(srcd);
		c.add(Calendar.DAY_OF_MONTH, days);
		
		d = c.getTime();
		
		return d;
		
	}
	
	//增加小时
	public static Date addHours(Date srcd,int hours) throws ParseException{
		Date d = null;
		
		Calendar c = Calendar.getInstance();
		c.setTime(srcd);
		c.add(Calendar.HOUR, hours);
		d = c.getTime();
		
		return d;
		
	}
	
	
	
	public static String getAllRoomCodeInOneAccount(String checkInId,Session session){
		
		try {
			
			String roomCodes = "";
			
			String hql = "select rm.roomCode " +
						" from RoomManage rm join rm.roomCheckInRels rc " +
						"  where rc.checkInBill.checkInId=:checkInId " +
						"  and rc.dicRoomFlag=:dicRoomFlag " +
						"  order by rm.roomCode ";
			
			Query q = session.createQuery(hql);
			q.setString("checkInId", checkInId);
			q.setString("dicRoomFlag", DictionaryConst.DIC_ROOM_FLAG_NORMALCHECKIN);
			
			List list = q.list();
			
			for(int i=0;i<list.size();i++){
				roomCodes+=list.get(i).toString();
				if(i<list.size()-1) roomCodes+=", ";
			}
			
			return roomCodes;
			
		} catch (RuntimeException re) {
			throw re;
		}
		
	}
	
	
	//酒店名
	public static String getHotelName(Session session){
		log.debug("getHotelName(Session session)  ");
		
		try {
			
				String hql = null;
				Query q = null;
				List list = null;
				
				String hotelName  = "";
				
				String orgCode = EfResourceContext.getInstance().getParamValue(ORG_CODE);
				
				hql = "from EfOrg o where o.orgCode=:orgCode";
				q = session.createQuery(hql);
				q.setString("orgCode", orgCode);
				list = q.list();
				
				if(list.size()>0){
					EfOrg efOrg = (EfOrg) list.get(0);
					hotelName  = efOrg.getOrgName();
				}
				
				return hotelName;
				
		} catch (RuntimeException re) {
			log.error("getHotelName(Session session) failed", re);
			throw re;
		}
		
	}
	
	//生成SQL的in语句条件
	public static String getInCondition(Set<String> set,String dataType){
		
		StringBuilder sb = new StringBuilder("(");
		
		boolean first = true;
		
		for(String s:set){
			if(first)
				first = false;
			else
				sb.append(",");
			
			if(dataType.equals(TYPE_STRING))
				sb.append("'").append(s).append("'");
			else
				sb.append(s);
		}
		
		if(!first)//如果至少拼接一次
			return sb.append(")").toString();
		else
			return null;
	}
	
	//根据入住单ID返回入住用户名
	public static Map<String,String> getCheckInNameMapFromCheckInIdSet(
										Session session,Set<String> allIdSet,
										String mode){
		
		Map<String,String> idNameMap = new HashMap<String,String>();
		
		String teamNameSql = "select TEAM_NAME NAME,CHECK_IN_BILL_ID"
				+ " from TEAM_CHECK_IN_BILL where CHECK_IN_BILL_ID in ";
		
		String personalSql = "select GUEST_NAME NAME,CHECK_IN_BILL_ID"
				+ " from PERSONAL_CHECK_IN_BILL where CHECK_IN_BILL_ID in ";
		
		Set<String> perSelectIdSet = new HashSet<String>();
		
		int idCount = 0;
		
		for(String checkInId:allIdSet){
			
			idCount++;
			
			perSelectIdSet.add(checkInId);
			
			if(perSelectIdSet.size()==inConditionMaxCount||idCount==allIdSet.size()){
				
				String sql = null;
				
				String inCondition = getInCondition(perSelectIdSet,TYPE_STRING);
				
				if(mode.equals(TEAM))
					sql = teamNameSql+inCondition;
				else
					sql = personalSql+inCondition;
					
				System.out.println("@@@@@sql:"+sql);
				
				//把查询到的保存起来
				List<Map<String,Object>> tempList = DaoUtil.selectBySql(sql, session);
				for(Map<String,Object> m : tempList){
					idNameMap.put(m.get("CHECK_IN_BILL_ID").toString(),
							m.get("NAME").toString());
				}
				
				perSelectIdSet = new HashSet<String>();
			}
			
		}
		
		System.out.println("allIdSet.size()==idNameMap.size():"+allIdSet.size()+"=="+idNameMap.size());
		
		return idNameMap;
		
	}
	
	//根据传入的roomCheckInRel表ID集合，返回对应的赔偿金额汇总
	public static Map<String,String> getSumCompensationFromRoomCheckInRelIds(
			Session session,Set<String> roomCheckInRelIdSet){
		
		Map<String,String> rsSumMap = new HashMap<String,String>();
		
		String sqlHead = "select sum(REL_PRICE) REL_PRICE_SUM,CHECK_IN_ID "
				+ "from GUEST_COMPENSATION where DOC_IS_PAY = "
				+ DictionaryConst.DIC_IS_PAY_NO + " and CHECK_IN_ID in ";
		
		String sqlEnd = " group by CHECK_IN_ID";
		
		Set<String> perSelectIdSet = new HashSet<String>();
		
		int idCount = 0;
		
		for(String checkInId:roomCheckInRelIdSet){
			
			idCount++;
			
			perSelectIdSet.add(checkInId);
			
			if(perSelectIdSet.size()==inConditionMaxCount||idCount==roomCheckInRelIdSet.size()){
				
				String sql = null;
				
				String inCondition = getInCondition(perSelectIdSet,TYPE_STRING);
				
				sql = sqlHead+inCondition+sqlEnd;
					
				System.out.println("$$$$sql:"+sql);
				
				//把查询到的保存起来
				List<Map<String,Object>> tempList = DaoUtil.selectBySql(sql, session);
				for(Map<String,Object> m : tempList){
					rsSumMap.put(m.get("CHECK_IN_ID").toString(),
							m.get("REL_PRICE_SUM").toString());
				}
				
				perSelectIdSet = new HashSet<String>();
			}
			
		}
		
		return rsSumMap;
	}
	
	//根据传入的checkInBill_id集合，返回对应的消费金额汇总
	public static Map<String,String> getSumConsumptonFromCheckInIds(
			Session session,Set<String> checkInIdSet){
		
		Map<String,String> rsSumMap = new HashMap<String,String>();
		
		String sqlHead = "select ROOM_ID,CHECK_IN_ID,sum(REAL_PRICE) REAL_PRICE"
				+ " from ROOM_CONSUMPTON_LOG where CHECK_IN_ID in ";
		
		String sqlEnd = " group by ROOM_ID,CHECK_IN_ID";
		
		Set<String> perSelectIdSet = new HashSet<String>();
		
		int idCount = 0;
		
		for(String checkInId:checkInIdSet){
			
			idCount++;
			
			perSelectIdSet.add(checkInId);
			
			if(perSelectIdSet.size()==inConditionMaxCount||idCount==checkInIdSet.size()){
				
				String sql = null;
				
				String inCondition = getInCondition(perSelectIdSet,TYPE_STRING);
				
				sql = sqlHead+inCondition+sqlEnd;
				
				System.out.println("###@@@sql:"+sql);
				
				//把查询到的保存起来
				List<Map<String,Object>> tempList = DaoUtil.selectBySql(sql, session);
				for(Map<String,Object> m : tempList){
					rsSumMap.put(m.get("CHECK_IN_ID").toString()+"_"+
							m.get("ROOM_ID").toString(),
							m.get("REAL_PRICE").toString());
				}
				
				perSelectIdSet = new HashSet<String>();
			}
			
		}
		
		return rsSumMap;
	}
	
}
